<!DOCTYPE html>
<html lang="zh">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" />
    <title>数据源列表 - 浩瀚后台管理系统模板</title>
    <link rel="icon" href="favicon.ico" type="image/ico">
    <meta name="keywords" content="Sql-Web,浩瀚,后台模板,后台管理系统,浩瀚HTML模板">
    <meta name="description" content="Sql-Web是一个基于Bootstrap v3.3.7的后台管理系统的HTML模板。">
    <meta name="author" content="浩瀚">
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/materialdesignicons.min.css" rel="stylesheet">
    <link href="css/style.min.css" rel="stylesheet">
    <!-- 自定义样式 -->
    <style>
        .form-inline .form-control,
        .form-inline .btn {
            height: 48px;
            /* 设置统一的高度 */
            vertical-align: middle;
            /* 垂直居中 */
        }

        #datasource-select {
            width: auto;
            /* 根据内容自动调整宽度 */
        }

        #sql-input {
            width: 50%;
            /* 根据需要调整宽度 */
        }

        .table-responsive {
            margin-top: 20px;
            font-size: 0.9em;
            /* 缩小表格字体尺寸 */
        }


        #pagination {
            display: flex;
            justify-content: flex-end;
        }

        td {
            max-width: 150px;
            /* 设置单元格最大宽度 */
            overflow: hidden;
            /* 隐藏超出部分 */
            text-overflow: ellipsis;
            /* 显示省略号 */
            white-space: nowrap;
            /* 不换行 */
        }

        td:hover {
            overflow: visible;
            /* 鼠标悬浮时显示全部内容 */
            white-space: normal;
            /* 允许换行 */
            position: absolute;
            background: #FFF;
            z-index: 100;
            /* 确保悬浮内容在其他内容上方 */
        }


        .autocomplete-suggestions {
            padding: 0;
            margin: 0;
            list-style: none;
            position: absolute;
            z-index: 9999;
        }

        .autocomplete-suggestion {
            background-color: #fff;
            border-bottom: 1px solid #d4d4d4;
            padding: 1px 10px;
            cursor: pointer;
        }

        .autocomplete-selected {
            background-color: #f0f0f0;
        }

        .autocomplete-suggestions strong {
            font-weight: normal;
            color: #3399ff;
        }

        .autocomplete-group {
            padding: 2px 5px;
        }

        .autocomplete-group strong {
            display: block;
            border-bottom: 1px solid #000;
        }

        .sql-input-container {
            position: relative;
            width: 80%;
            margin: auto;
        }



        .autocomplete-suggestions {
            padding: 0;
            margin: 0;
            list-style: none;
            position: absolute;
            z-index: 9999;
            top: 100%;
            /* 紧贴输入框底部 */
            left: 0;
            right: 0;
            width: 100%;
            /* 宽度与输入框相同 */
            border: none;
            /* 移除边框 */
            max-height: 200px;
            /* 限制最大高度 */
            overflow-y: auto;
            /* 超出时显示滚动条 */
            background-color: #ffffff;
            /* 背景色 */
            border-radius: 4px;
            /* 圆角 */
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            /* 阴影效果 */
            font-size: 16px;
            /* 文字大小 */
        }

        .autocomplete-suggestion {
            padding: 4px 10px;
            /* 减小上下内边距 */
            cursor: pointer;
            text-align: left;
            /* 内容右对齐 */
            border-bottom: none;
            /* 移除分隔线 */
            white-space: normal;
            /* 允许换行 */
        }

        .autocomplete-suggestion:hover,
        .autocomplete-suggestion.autocomplete-selected {
            background-color: #f0f0f0;
            /* 为悬停或选中的建议项设置高亮背景色 */
        }

        .autocomplete-suggestions strong {
            font-weight: normal;
            color: #3399ff;
            /* 为匹配的文本片段设置高亮颜色 */
        }
    </style>



</head>

<body>
    <div class="container-fluid p-t-15">
        <div class="row">
            <div class="col-xs-12">
                <div class="form-inline text-center">
                    <!-- 替换为 Bootstrap 下拉菜单 -->
                    <div class="btn-group">
                        <button type="button" id="datasource-select" class="btn secondary dropdown-toggle"
                            data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" style="width: 160px;">
                            选择数据源 <span class="caret"></span>
                        </button>
                        <ul id="datasource-menu" class="dropdown-menu">
                            <!-- 数据源选项将在这里动态加载 -->
                        </ul>
                    </div>
                    <input class="form-control" type="text" id="sql-input" autocomplete="off" placeholder="输入待执行SQL...">
                    <button type="button" id="execute-btn" class="btn btn-lg btn-primary">执行</button>
                </div>
            </div>
        </div>
        <div class="row">
            <div class="col-xs-12">
                <!-- 表格的容器，用于实现滚动条 -->
                <div id="results" class="table-responsive">
                    <!-- 结果表格将在这里动态生成 -->
                </div>
                <!-- 分页的容器 -->
                <nav aria-label="Page navigation">
                    <ul id="pagination" class="pagination justify-content-end">
                        <!-- 分页按钮将在这里动态生成 -->
                    </ul>
                </nav>

            </div>
        </div>
    </div>
    <!-- 美观协调的模态框 -->
    <div class="modal fade" id="balancedAlertModal" tabindex="-1" role="dialog">
        <div class="modal-dialog modal-dialog-centered" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">操作提示</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
                <div class="modal-body">
                    <p id="balancedAlertModalMessage">这里是用户需要知道的信息。</p>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-primary" data-dismiss="modal">确认</button>
                </div>
            </div>
        </div>
    </div>
    <input type="hidden" id="selected-datasource-id">





    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/bootstrap.min.js"></script>
    <script type="text/javascript">
        var sqlKeywords = ["SELECT", "FROM", "WHERE", "LIMIT", "ORDER BY", "GROUP BY", "INSERT INTO", "VALUES", "UPDATE", "SET", "DELETE", "CREATE TABLE", "DROP TABLE", "ALTER TABLE", "JOIN", "INNER JOIN", "LEFT JOIN", "RIGHT JOIN"];
        var tableNames = []; // 初始化为空数组
        var dataSourceList = []; // 存储数据源列表


        document.addEventListener('DOMContentLoaded', function () {
            var autocompleteList = document.createElement('ul');
            autocompleteList.classList.add('autocomplete-suggestions');

            var sqlInput = document.getElementById('sql-input');
            sqlInput.parentNode.appendChild(autocompleteList);

            // 动态设置自动完成框的宽度和顶部位置
            function updateSuggestionBoxPosition() {
                // ...代码以获取sqlInput和autocompleteList...
                var sqlInputRect = sqlInput.getBoundingClientRect();
                autocompleteList.style.width = sqlInput.offsetWidth + 'px'; // 宽度与输入框相同
                autocompleteList.style.top = (sqlInputRect.bottom + window.scrollY) + 'px'; // 紧贴输入框底部
                autocompleteList.style.left = sqlInputRect.left + 'px'; // 左对齐与输入框
            }

            function filterSuggestions(input) {
                return sqlKeywords.concat(tableNames).filter(function (keyword) {
                    return keyword.toLowerCase().startsWith(input.toLowerCase());
                }).slice(0, 10);  // 只取匹配的前10个结果
            }

            function setActiveSuggestion(index) {
                removeActiveSuggestion();
                if (autocompleteList.children[index]) {
                    autocompleteList.children[index].classList.add('autocomplete-selected');
                }
            }

            function removeActiveSuggestion() {
                var currentActive = document.querySelector('.autocomplete-selected');
                if (currentActive) {
                    currentActive.classList.remove('autocomplete-selected');
                }
            }

            function fillInputAndClearSuggestions(value) {
                var terms = sqlInput.value.split(' ');
                terms.pop();
                terms.push(value);
                sqlInput.value = terms.join(' ') + ' ';
                autocompleteList.innerHTML = '';
                sqlInput.focus();
            }

            sqlInput.addEventListener('input', function () {
                updateSuggestionBoxPosition(); // 更新自动完成框的位置和宽度
                var input = this.value.split(' ').pop();
                if (!input) {
                    autocompleteList.innerHTML = '';
                    return;
                }

                var suggestions = filterSuggestions(input);
                autocompleteList.innerHTML = '';
                suggestions.forEach(function (suggested) {
                    var listItem = document.createElement('li');
                    listItem.textContent = suggested;
                    listItem.classList.add('autocomplete-suggestion');
                    listItem.onclick = function () {
                        fillInputAndClearSuggestions(this.textContent);
                    };
                    autocompleteList.appendChild(listItem);
                });
            });

            var activeSuggestionIndex = -1;

            sqlInput.addEventListener('keydown', function (e) {
                var listLength = autocompleteList.children.length;
                if (e.key === 'ArrowDown') {
                    if (activeSuggestionIndex < listLength - 1) {
                        activeSuggestionIndex++;
                    } else {
                        activeSuggestionIndex = 0; // 循环到第一个
                    }
                    setActiveSuggestion(activeSuggestionIndex);
                    e.preventDefault(); // 防止光标移动
                } else if (e.key === 'ArrowUp') {
                    if (activeSuggestionIndex > 0) {
                        activeSuggestionIndex--;
                    } else {
                        activeSuggestionIndex = listLength - 1; // 循环到最后一个
                    }
                    setActiveSuggestion(activeSuggestionIndex);
                    e.preventDefault();
                } else if (e.key === 'Enter' && activeSuggestionIndex !== -1) {
                    fillInputAndClearSuggestions(autocompleteList.children[activeSuggestionIndex].textContent);
                    e.preventDefault(); // 防止表单提交
                }
            });

            document.addEventListener('click', function (e) {
                if (e.target !== sqlInput) {
                    autocompleteList.innerHTML = '';
                    activeSuggestionIndex = -1;
                }
            });
        });

        // 如果窗口大小发生变化也需要更新位置
        // window.addEventListener('resize', updateSuggestionBoxPosition);
        // 修改fetchDataSourceList函数以更新dataSourceList和tableNames
        function fetchDataSourceList() {
            $.ajax({
                url: '/datasource/list',
                type: 'post',
                dataType: 'json',
                success: function (response) {
                    if (response.code === 200 && response.data) {
                        dataSourceList = response.data; // 保存数据源列表
                        var menu = $('#datasource-menu');
                        menu.empty();
                        response.data.forEach(function (datasource, index) {
                            var listItem = $('<li><a href="#!" data-id="' + datasource.id + '">' + datasource.name + '</a></li>');
                            listItem.find('a').on('click', function (e) {
                                e.preventDefault();
                                var selectedText = $(this).text();
                                var selectedId = $(this).data('id');
                                $('#datasource-select').text(selectedText + ' ').append('<span class="caret"></span>');
                                $('#selected-datasource-id').val(selectedId);

                                // 更新tableNames数组
                                var selectedDataSource = dataSourceList.find(function (ds) {
                                    return ds.id == selectedId;
                                });
                                if (selectedDataSource && selectedDataSource.tables) {
                                    tableNames = selectedDataSource.tables;
                                } else {
                                    tableNames = [];
                                }
                            });
                            menu.append(listItem);
                            // 默认选中第一个数据源并触发点击事件
                            if (index === 0) {
                                listItem.find('a').trigger('click');
                            }
                        });
                    } else {
                        showAlertModal('获取数据源列表失败: ' + response.msg);
                    }
                },
                error: function (jqXHR, textStatus, errorThrown) {
                    showAlertModal('请求数据源列表失败: ' + textStatus);
                }
            });
        }


        // 定义更新分页按钮的函数
        function updatePagination(totalPages, currentPage, data, pageSize) {
            var paginationUl = $('#pagination').empty();
            var maxPageButtons = 5; // 最多显示的分页按钮数
            var halfWay = Math.ceil(maxPageButtons / 2);
            var startPage = currentPage - halfWay + 1;
            startPage = Math.max(startPage, 1);
            var endPage = startPage + maxPageButtons - 1;
            endPage = Math.min(endPage, totalPages);

            // 添加“首页”和“上一页”按钮
            if (currentPage > 1) {
                paginationUl.append($('<li class="page-item"></li>').append($('<a class="page-link">首页</a>').attr('href', '#').attr('data-page', 1)));
                paginationUl.append($('<li class="page-item"></li>').append($('<a class="page-link">上一页</a>').attr('href', '#').attr('data-page', currentPage - 1)));
            }

            // 添加中间的分页按钮
            for (var i = startPage; i <= endPage; i++) {
                var li = $('<li class="page-item"></li>').append($('<a class="page-link"></a>').text(i).attr('href', '#').attr('data-page', i));
                if (i === currentPage) li.addClass('active');
                paginationUl.append(li);
            }

            // 添加“下一页”和“末页”按钮
            if (currentPage < totalPages) {
                paginationUl.append($('<li class="page-item"></li>').append($('<a class="page-link">下一页</a>').attr('href', '#').attr('data-page', currentPage + 1)));
                paginationUl.append($('<li class="page-item"></li>').append($('<a class="page-link">末页</a>').attr('href', '#').attr('data-page', totalPages)));
            }

            // 绑定分页按钮点击事件
            paginationUl.find('a').on('click', function (e) {
                e.preventDefault();
                var selectedPage = parseInt($(this).data('page'));
                renderTable(data, selectedPage, pageSize);
            });
        }

        // 定义渲染表格和分页的函数
        function renderTable(data, page = 1, pageSize = 10) {
            // 清空现有表格和分页
            $('#results').empty();
            $('#pagination').empty();

            // 计算分页数据
            const offset = (page - 1) * pageSize;
            const pagedData = data.slice(offset, offset + pageSize);

            // 创建表格元素
            var table = $('<table></table>').addClass('table table-striped table-bordered');
            var thead = $('<thead></thead>');
            var headerRow = $('<tr></tr>');
            if (pagedData.length > 0) {
                Object.keys(pagedData[0]).forEach(function (key) {
                    headerRow.append($('<th></th>').text(key));
                });
                thead.append(headerRow);
                table.append(thead);
            }

            var tbody = $('<tbody></tbody>');
            pagedData.forEach(function (row) {
                var tableRow = $('<tr></tr>');
                Object.values(row).forEach(function (val) {
                    tableRow.append($('<td></td>').text(val));
                });
                tbody.append(tableRow);
            });
            table.append(tbody);
            $('#results').append(table);

            // 计算分页的总页数
            const totalPages = Math.ceil(data.length / pageSize);

            // 生成并显示分页按钮
            updatePagination(totalPages, page, data, pageSize);
        }

        $(document).ready(function () {

            fetchDataSourceList();


            // 显示美观协调的模态弹窗提示的函数
            function showAlertModal(message) {
                $('#balancedAlertModalMessage').text(message);
                $('#balancedAlertModal').modal('show');
            }


            // 执行按钮点击事件
            $('#execute-btn').click(function () {
                var dataSourceId = $('#selected-datasource-id').val();
                var sql = $('#sql-input').val().trim();
                // 检查数据源是否已选择
                if (dataSourceId === '选择数据源' || !dataSourceId) {
                    showAlertModal('请选择一个数据源。');
                    return; // 停止函数执行
                }
                // 检查SQL语句是否已输入
                if (!sql) {
                    showAlertModal('请输入SQL语句。');
                    return; // 停止函数执行
                }
                var requestData = {
                    "dataSourceId": dataSourceId,
                    "sqlStatement": sql
                };

                $.ajax({
                    url: '/sql/executeSql',
                    type: 'post',
                    contentType: 'application/json',
                    data: JSON.stringify(requestData),
                    dataType: 'json',
                    success: function (response) {
                        if (response.code === 200 && response.data) {
                            var data = response.data; // 假设这是您的数据源
                            renderTable(response.data);
                        } else {
                            showAlertModal('没有结果。');
                        }
                    },
                    error: function () {
                        showAlertModal('执行SQL时发生错误。');
                    }
                });
            });
        });

    </script>
</body>

</html>